package com.kcsj.dao;
//李延杰
import com.kcsj.domain.Product;
import com.kcsj.util.Condition;
import com.kcsj.util.JdbcHelper;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.List;

public class ProductDao {
    private static final ProductDao productDao = new ProductDao();
    private ProductDao(){
    }

    public static ProductDao getInstance(){
        return productDao;
    }

    //查找school表中所有数据
    public Collection<Product> findAll(List<Condition> conditionList, Connection conn) throws SQLException {
        ArrayList<Product> deliveryPeoples = new ArrayList<>();
        //预编译方式进行查询
        StringBuilder sql = new StringBuilder("select * from product");
        if(conditionList!=null){
            String whereClause = Condition.toWhereClause(conditionList);
            sql.append(whereClause);
        }
        PreparedStatement preparedStatement = conn.prepareStatement(sql.toString());
        ResultSet resultSet = preparedStatement.executeQuery();
        while (resultSet.next()){
            String productNo = resultSet.getString(1);
            String name = resultSet.getString(2);
            String picPath = resultSet.getString(3);
            Product product = new Product(productNo,name,picPath);
            deliveryPeoples.add(product);
        }
        JdbcHelper.close(resultSet,preparedStatement);
        return deliveryPeoples;
    }


    //根据id值查找Administrator表中相应数据
    public Product find(String productNo, Connection conn) throws SQLException {
        Product product = null;
        //预编译方式进行查询
        String s = "select * from product where  productNo = ?";
        PreparedStatement preparedStatement = conn.prepareStatement(s);
        preparedStatement.setString(1,productNo);
        ResultSet resultSet = preparedStatement.executeQuery();
        //将resultSet转换为json字符串
        if (resultSet.next()){
            String name = resultSet.getString(2);
            String picPath = resultSet.getString(3);
            product = new Product(productNo,name,picPath);
        }
        //关闭连接
        JdbcHelper.close(resultSet,preparedStatement);
        return product;
    }

    //修改school表中的数据
    public boolean update(Product product, Connection conn) throws SQLException {
        //预编译方式进行修改数据
        String s = "update product set name=? where productNo = ?";
        PreparedStatement preparedStatement = conn.prepareStatement(s);
        preparedStatement.setString(1,product.getName());
        preparedStatement.setString(2,product.getProductNo());
        //如果执行影响行数大于0，则修改成功
        int i = preparedStatement.executeUpdate();
        //关闭连接
        JdbcHelper.close(preparedStatement);
        return i > 0;
    }
    //向Administrator表中添加数据
    public int add(Product product, Connection conn) throws SQLException {
        //预编译方式进行增加数据
        String s = "insert into product(productNo,name,picPath) value (?,?,?)";
        PreparedStatement preparedStatement = conn.prepareStatement(s);
        preparedStatement.setString(1,product.getProductNo());
        preparedStatement.setString(2,product.getName());
        preparedStatement.setString(3,product.getPicPath());
        //如果执行影响行数大于0，则增加成功
        int id = preparedStatement.executeUpdate();
        //关闭连接
        JdbcHelper.close(preparedStatement);
        return id;
    }
    //在school表根据id删除相应数据
    public boolean delete(String productNo, Connection conn) throws SQLException {
        //预编译方式进行删除数据
        String s = "delete from product where  productNo = ?";
        PreparedStatement preparedStatement = conn.prepareStatement(s);
        preparedStatement.setString(1,productNo);
        //如果执行影响行数大于0，则增加成功
        int i = preparedStatement.executeUpdate();
        //关闭连接
        JdbcHelper.close(preparedStatement);
        return i > 0;
    }

}
